package com.zd.commodity.biz.impl;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;

import utils.ExcelUtil;
import utils.Parameters;

import com.zd.commodity.dao.IBaseDao;

/**
 * 基本业务逻辑类
 * @author Administrator
 *
 * @param <T>
 */
public class BaseBiz<T>  {

	private IBaseDao baseDao;
	
	public void setBaseDao(IBaseDao baseDao) {
		this.baseDao = baseDao;
	}
	
	/**
	 * 导出excel
	 * @param clasz 
	 * @param sql
	 * @param argMap
	 * @param firstResult
	 * @param maxResults
	 * @return
	 */
	public List getExcelExportListBySql(Class clasz,String sql, Map<String, String> argMap, int firstResult, int maxResults){
		return baseDao.getPagedListBySql(clasz, sql, argMap, firstResult, maxResults);
	}

	/**
	 * 条件查询
	 * @param t1
	 * @param t2
	 * @param param
	 * @return
	 */
	public List<T> getList(T t1,T t2,Object param) {
		// TODO Auto-generated method stub
		return baseDao.getList(t1,t2,param);
	}

	/**
	 * 分页查询
	 * @param t1
	 * @param t2
	 * @param param
	 * @param firstResult
	 * @param maxResults
	 * @return
	 */
	public List<T> getListByPage(T t1,T t2,Object param, int firstResult, int maxResults) {
		// TODO Auto-generated method stub
		return baseDao.getListByPage(t1,t2,param, firstResult, maxResults);
	}

	/**
	 * 统计个数
	 * @param t1
	 * @param t2
	 * @param param
	 * @return
	 */
	public long getCount(T t1, T t2, Object param) {
		// TODO Auto-generated method stub
		return baseDao.getCount(t1, t2, param);
	}

	/**
	 * hql分页查询
	 * @param t1
	 * @param t2
	 * @param param
	 * @param firstResult
	 * @param maxResults
	 * @return
	 */
    public List getPagedListBySql(Class clasz,String sql, Map<String, String> argMap, int firstResult, int maxResults){
    	return baseDao.getPagedListBySql(clasz, sql, argMap, firstResult, maxResults);
    }
 
    /**
	 * hql统计个数
	 * @param t1
	 * @param t2
	 * @param param
	 * @return
	 */
	public long getPagedListCountBySql(String sql, Map<String, String> argMap){
		return baseDao.getPagedListCountBySql(sql, argMap);
	}
	
	
	/**
	 * 增加
	 * @param t
	 */
	public void add(T t) {
		baseDao.add(t);	
	}

	/**
	 * 删除
	 * @param id
	 */
	public void delete(Long id) {
		baseDao.delete(id);		
	}

	/**
	 * 查找实体
	 * @param id
	 * @return
	 */
	public T get(Long id) {
		return (T) baseDao.get(id);
	}

	/**
	 * 修改
	 * @param t
	 */
	public void update(T t) {
		baseDao.update(t);		
	}

	public List<String> checkExcelRepeatBatch(List<String[]> excelData,String sql,int[] concatCols) {
		StringBuilder sb = new StringBuilder("'");
		
		for (String[] s : excelData) {
			//拼接列
			for(int i=0;i<concatCols.length;i++){
				sb.append(s[concatCols[i]]);
			}
			sb.append("','");
		}
		Map<String, String> argMap = new HashMap<String, String>();
		argMap.put("inParams", sb.substring(0, sb.lastIndexOf(",'")));
		List listBySql = baseDao.getListBySql(sql, argMap);
		return listBySql;
	}
	/**
	 * 读取上传的excel map 验证信息 key 是execl第几个字段 从0开始 value "notNull"是不为空，
	 * map 
	 * 	key 字段的位置  从0开始  
	 *  value  查询是否名称重复由三段组成，用|分割   queryRepeat  
	 *  	        根据名称查询id或信息  由四段组成 ， 用|分割    queryNameByIdorlist 
	 *  	1、sqlid
	 *  	2、展示字段（验证是重复该字段）
	 *  	2、表名（如果是多个表的话，就是连接的句子如 product_attr a LEFT JOIN category c on a.category_id = c.id ），
	 *  	2、字段名（如果两个查询条件，第二个字段需要写出位置如：a.name-c.name#1     从零开始  1 是第二个字段）
	 */
	public List<String[]> readExecl(HashMap<Integer, String> checkmap) {
		String excelUrl = "";
		try {
			String path = ServletActionContext.getRequest().getSession().getServletContext().getRealPath(File.separator + Parameters.UPLOADFILEPATH);
			Map<String, Object> map = Parameters.getUploadParameters();
			File f = (File) map.get("file");
			String fileName = (String) map.get("fileFileName");
			if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
				String newFileName = "";
				FileInputStream inputStream = new FileInputStream(f);
				newFileName = fileName.substring(0, fileName.lastIndexOf("."));
				newFileName = newFileName+ fileName.substring(fileName.lastIndexOf("."));
				FileOutputStream outputStream = new FileOutputStream(path+ File.separator + newFileName);
				byte[] buf = new byte[1024];
				int length = 0;
				while ((length = inputStream.read(buf)) != -1) {
					outputStream.write(buf, 0, length);
				}
				inputStream.close();
				outputStream.close();
				excelUrl = path + File.separator + newFileName;
				// 结果集
				List<String[]> list = new ArrayList<String[]>();
				Workbook hssfworkbook = null;
				try {
					hssfworkbook = new XSSFWorkbook(new FileInputStream(excelUrl));
				} catch (Exception ex) {
					hssfworkbook = new HSSFWorkbook(new FileInputStream(excelUrl));
				}
				// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
				Sheet hssfsheet = hssfworkbook.getSheetAt(0);
				int rowStart = 1;
				int columnStart = 0;
				boolean falg = false;
				if (hssfworkbook.getSheet("WBS") != null) {
					falg = true;
					rowStart = 3;
					columnStart = 1;
					hssfsheet = hssfworkbook.getSheet("WBS");
				}
				// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数getPhysicalNumberOfRows
				// TODO 获取excel行数需要更改
				int rows = ExcelUtil.getMaxRowsOfSheet(hssfsheet);
				DecimalFormat df = new DecimalFormat("0.00");
				int col = hssfsheet.getRow(0).getLastCellNum();
				big: for (int j = rowStart; j < rows; j++) {
					//循环每一行
					Row hssfrow = hssfsheet.getRow(j);
					if (hssfrow != null) {
						// 单行数据
						String[] arrayString = new String[col];
						for (int i = columnStart; i < col; i++) {
							//循环每一个单元格
							// 是否校验为空
							String checkContent = checkmap.get(i);
							boolean checkFlag = false;
							if ("notNull".equals(checkContent)) {
								checkFlag = true;
							}
							Cell cell = hssfrow.getCell(i);
							if (cell == null) {
								if (falg) {
									arrayString[i - 1] = "";
								} else {
									arrayString[i] = "";
								}
								if (checkFlag) {
									list.clear();
									String[] falseFlag = {"0",ExcelUtil.excelErrorMsg(j,i,null,null,null,3) };
									list.add(falseFlag);
									break big;
								}
							} else if (cell.getCellType() == 0) {
								//如果EXCEL表格中的数据类型为数字类型
								if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
									cell.setCellType(Cell.CELL_TYPE_STRING);
									arrayString[i] = ExcelUtil.getStringVal(cell);
									/*if (HSSFDateUtil.isCellDateFormatted(cell)) {
										Date d = cell.getDateCellValue();
										DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
										if (falg) {
											arrayString[i - 1] = formater.format(d);
										} else {
											arrayString[i] = formater.format(d);
										}
									} else {
										if (falg) {
											arrayString[i - 1] = new BigDecimal(cell.getNumericCellValue()).longValue()+ "";
										} else {
											arrayString[i] = ExcelUtil.getStringVal(cell);
										}

									}*/
								}
							} else {// 如果EXCEL表格中的数据类型为字符串型
								//查询是否重复,如果重复了，中断循环，提醒用户
								if (checkContent != null && !"notNull".equals(checkContent) && checkContent.contains("Repeat")) {
									String[] checkSplit = checkContent.split("\\|");
									Map<String, String> argMap = new HashMap();
									argMap.put("tableName",checkSplit[1] );
									String columName = checkSplit[2];
									boolean flag = false;
									int columnum = 0;
									if(columName.contains("-")){
										//循环条件
										String[] columNames = columName.split("-");
										argMap.put("columName", columNames[0]);
										argMap.put("value", cell.getStringCellValue().trim());
										for (int k = 1; k < columNames.length; k++) {
											String[] colum = columNames[k].split("#");
											argMap.put("columName"+k, colum[0]);
											columnum = columnStart + Integer.valueOf(colum[1]);
											argMap.put("value"+k, hssfrow.getCell(columnum).getStringCellValue().trim());
										}
										flag = true;
									}else{
										argMap.put("columName", checkSplit[2]);
										argMap.put("value", cell.getStringCellValue().trim());
									}
									long pagedListCountBySql = baseDao.getPagedListCountBySql(checkSplit[0], argMap);
									if(pagedListCountBySql > 0 ){
										list.clear();
										if(flag){
											String[] falseFlag = {"0",ExcelUtil.excelErrorMsg(j,i,checkContent,cell.getStringCellValue().trim(),hssfrow.getCell(columnum).getStringCellValue().trim(),1) };
											list.add(falseFlag);
										}else{
											String[] falseFlag = {"0",ExcelUtil.excelErrorMsg(j,i,checkContent,cell.getStringCellValue().trim(),null,1) };
											list.add(falseFlag);
										}
										break big;
									}
								}
								//通过名称查询一个id或者多个id
								else if(checkContent != null && checkContent.contains("NameById")){
									String[] checkSplit = checkContent.split("\\|");
									Map<String, String> argMap = new HashMap();
									argMap.put("showColum",checkSplit[1] );
									argMap.put("tableName",checkSplit[2] );
									String columName = checkSplit[3];
									int columnum = 0;
									if(columName.contains("-")){
										//循环条件
										String[] columNames = columName.split("-");
										argMap.put("columName", columNames[0]);
										argMap.put("value", cell.getStringCellValue().trim());
										for (int k = 1; k < columNames.length; k++) {
											String[] colum = columNames[k].split("#");
											argMap.put("columName"+k, colum[0]);
											columnum = columnStart + Integer.valueOf(colum[1]);
											argMap.put("value"+k, hssfrow.getCell(columnum).getStringCellValue().trim());
										}
									}else{
										argMap.put("columName", checkSplit[3]);
										argMap.put("value", cell.getStringCellValue().trim());
									}
									
									List lists = baseDao.getListBySql(checkSplit[0], argMap);
									if(lists.size()==0){
										if(checkSplit[3].contains("a.name-c.id#0-g.id#1")){
											//上传数据数据字段的时候比较麻烦，就判断了下，如果可以根据分类、分组、属性查不到id，因为需要添加一个新的属性，所以不报错。
										}else{
											list.clear();
											String[] falseFlag = {"0",ExcelUtil.excelErrorMsg(j,i,checkContent,cell.getStringCellValue().trim(),null,2) };
											list.add(falseFlag);
											break big;
										}
										
									}
									if(!"id".equals(checkSplit[1])){
										//如果不是单独查询id的话，就是查询多个id，需要拼一下
										StringBuffer s = new StringBuffer();
										for (Object object : lists) {
											if(object!=null){
												Object[] r = (Object[]) object;
												for (int k = 0; k < r.length; k++) {
													String result = r[k].toString();
													s.append(result);
													if(checkSplit[3].contains("a.name-c.id#0-g.id#1") ){
														s.append("#id");
													}else{
														s.append("-");
													}
												}
												s.append("|");
											}
										}
										if(lists.size()!=0 &&!lists.toString().equals("[]")){
											cell.setCellValue(s.toString());
										}
									}else if(lists.size()!=0 &&!lists.toString().equals("[]")){
										cell.setCellValue(lists.get(0).toString());
									}
								}
								if (falg) {
									arrayString[i - 1] = cell.getStringCellValue()
											.trim();
								} else {
									arrayString[i] = cell.getStringCellValue().trim();
								}

							}
							if (falg && i == col - 1) {
								arrayString[i] = "";
							}

						}
						list.add(arrayString);
					}
				}
				return list;
			}
			return null;
		} catch (Exception e) {
	            e.printStackTrace();  
		} finally {
			new File(excelUrl).delete();
		}
		return null;
	}
	
}



